package com.spi.soconversion.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.spi.soconversion.base.SOCException;
import com.spi.soconversion.base.definition.COFIOrderLine;
import com.spi.soconversion.util.QueryConstants;
import com.spi.soconversion.util.QueryReader;
import com.spi.soconversion.util.ResourceProvider;

/**
 * A DAO class for Order implementation
 * 
 * @author XSPID41
 *
 */
public class OrderDAOImpl implements OrderDAO, OpenSalesOrderConstants {

	static private org.apache.commons.logging.Log log = 
	    org.apache.commons.logging.LogFactory.getLog(OrderDAOImpl.class);

	public ResourceProvider provider;		
	
	public OrderDAOImpl() {
		provider = ResourceProvider.getInstance();
	}
		
	/**
	 * Get a list of open sales orders
	 */
	public ArrayList<String> getOrders() throws SOCException {
		ArrayList<String> result = new ArrayList<String>();
		Statement ordersStatement = null;
		ResultSet rsOrders = null;
		
		String ordersQuery = QueryReader.getQuery(QueryConstants.GET_ALL_OPEN_SALES_ORDERS);
		try {
			ordersStatement = provider.getdbConnection().createStatement();
			rsOrders = ordersStatement.executeQuery(ordersQuery);
		
			while (rsOrders.next()) {
				result.add(rsOrders.getString("SERIAL_NUMBER"));			
			}
		} catch (SQLException sqle) {
			log.error("Error in getOrders()", sqle);
			sqle.printStackTrace();
			throw new SOCException("Error in retrieving orders - getOrders" , sqle);
		}
		
		try {
			if (rsOrders != null) {
				rsOrders.close();
			}
			
			if(ordersStatement!=null) {
				ordersStatement.close();
			}			
		}
		catch (SQLException sqle) {
			sqle.printStackTrace();
		}

		return result;				
	}

	/**
	 * Get a list of open sales orders
	 */
	public ArrayList<String> getOrdersForSize(String batchSize) throws SOCException {
		ArrayList<String> result = new ArrayList<String>();
		PreparedStatement orderStatement = null;
		ResultSet rsOrders = null;
		
		String ordersQuery = QueryReader.getQuery(QueryConstants.GET_ALL_OPEN_SALES_ORDERS_FOR_SIZE);
		try {
			orderStatement = provider.getdbConnection().prepareStatement(ordersQuery);
			orderStatement.setString(1, batchSize);
			rsOrders = orderStatement.executeQuery();			
			
			while (rsOrders.next()) {
				result.add(rsOrders.getString("SERIAL_NUMBER"));			
			}
		} catch (SQLException sqle) {
			log.error("Error in getOrders()", sqle);			
			sqle.printStackTrace();
			throw new SOCException("Error in retrieving orders - getOrdersForSize" , sqle);
		}
		
		try {
			if (rsOrders != null) {
				rsOrders.close();
			}
			
			if(orderStatement!=null) {
				orderStatement.close();
			}
		}
		catch (SQLException sqle) {
			sqle.printStackTrace();
		}

		return result;				
	}

	public ArrayList<String> getOrderNumber(String headerID) 
	throws SOCException {
		ArrayList<String> result = new ArrayList<String>();
		try {
			PreparedStatement orderStatement = null;
			ResultSet rsOrderLines = null;
			
			String orderQuery = QueryReader.getQuery(QueryConstants.GET_ORDER_NUMBER_FROM_HEADER_ID);		
			orderStatement = provider.getdbConnection().prepareStatement(orderQuery);
			orderStatement.setString(1, headerID);
			rsOrderLines = orderStatement.executeQuery();		
			
			while (rsOrderLines.next()) {
				result.add(rsOrderLines.getString(OpenSalesOrderConstants.ORDER_NUMBER));
			}
			
			try {
				if (rsOrderLines != null) {
					rsOrderLines.close();
				}
				
				if(orderStatement!=null) {
					orderStatement.close();
				}			
			}
			catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}
		catch(SQLException sqle) {
			sqle.printStackTrace();
			throw new SOCException("", sqle);
		}

		return result;		
	}
	
	/**
	 * Get an open sales order for a given order number
	 * @param orderNumber
	 */
	public ArrayList<COFIOrderLine> getOpenSaleOrder(String orderNumber) 
	throws SOCException {		
		PreparedStatement orderStatement = null;
		ResultSet rsOrderLines = null;
		ArrayList<COFIOrderLine> result = new ArrayList<COFIOrderLine>();
		
		String orderQuery = QueryReader.getQuery(QueryConstants.GET_AN_OPEN_SALES_ORDER);
		try {
			orderStatement = provider.getdbConnection().prepareStatement(orderQuery);
			orderStatement.setString(1, orderNumber);
			rsOrderLines = orderStatement.executeQuery();		
			
			while (rsOrderLines.next()) {
				COFIOrderLine order = new COFIOrderLine();
				setValues(order, rsOrderLines);
				result.add(order);
			}
		} catch (SQLException sqle) {
			log.error("Error in getOrders()", sqle);
			sqle.printStackTrace();
			throw new SOCException("Error in retrieving orders - getOpenSaleOrder" , sqle);
		}
		
		try {
			if (rsOrderLines != null) {
				rsOrderLines.close();
			}
			
			if(orderStatement!=null) {
				orderStatement.close();
			}			
		}
		catch (SQLException sqle) {
			sqle.printStackTrace();
		}

		return result;
	}	
		

	/**
	 * Fills the data from the ResultSet
	 * @param rs
	 * @param order
	 */
	private void setValues(COFIOrderLine order, ResultSet rs) throws SQLException {		
		order.setSerialNumber(rs.getInt(SERIAL_NUMBER));
		order.setHeaderID(rs.getInt(HEADER_ID));
		order.setOrderNumber(rs.getLong(ORDER_NUMBER));
		order.setOrderDate(rs.getTimestamp(ORDERED_DATE));
		order.setPartyId(rs.getString(PARTY_ID));
		order.setLineId(rs.getLong(LINE_ID));
		order.setLineTypeId(rs.getInt(LINE_TYPE_ID));
		order.setLineNumber(rs.getInt(LINE_NUMBER));
		order.setLineCategoryCode(rs.getString(LINE_CATEGORY_CODE));
		order.setHeaderBillToKey(rs.getLong(HEADER_BILL_TO_KEY));
		order.setHeaderShipToKey(rs.getLong(HEADER_SHIP_TO_KEY));		
		order.setLineBillToKey(rs.getLong(LINE_BILL_TO_KEY));
		order.setLineShipToKey(rs.getLong(LINE_SHIP_TO_KEY));		
		order.setHeaderAttribute3(rs.getString(HEADER_ATTRIB3));
		order.setHeaderAttribute5(rs.getString(HEADER_ATTRIB5));
		order.setEmpBatchNum(rs.getString(EMP_BADGE_NUM));
		order.setTaxExemptType(rs.getString(TAX_EXEMPT_TYPE));
		order.setTaxExemptCertificate(rs.getString(TAX_EXEMPT_CERT));
		order.setHeaderInvoiceToOrgId(rs.getInt(HEADER_INVOICE_TO_ORG_ID));
		order.setHeaderShipToOrgId(rs.getInt(HEADER_SHIP_TO_ORG_ID));
		order.setOrgCode(rs.getInt(ORG_CODE));
		order.setShipNode(rs.getInt(SHIP_NODE));
		order.setUnitSellingPrice(rs.getBigDecimal(UNIT_SELLING_PRICE));
		order.setUnitListPrice(rs.getBigDecimal(UNIT_LIST_PRICE));
		order.setOrderedQuantity(rs.getInt(ORDERED_QUANTITY));
		order.setScheduleShipDate(rs.getTimestamp(SCHEDULE_SHIP_DATE));
		order.setPromiseDate(rs.getTimestamp(PROMISE_DATE));
		order.setShippingMethodCode(rs.getString(SHIPPING_METHOD_CODE));
		order.setLinesShipFromOrgId(rs.getInt(LINES_SHIP_FROM_ORG_ID));
		order.setLinesShipToOrgId(rs.getInt(LINES_SHIP_TO_ORG_ID));
		order.setSubInventory(rs.getString(SUBINVENTORY));
		order.setTaxValue(rs.getBigDecimal(TAX_VALUE));
		order.setGiftRegLineNumber(rs.getString(GIFT_REG_LINE_NUM));
		order.setGiftRegNumber(rs.getString(GIFT_REG_NUM));
		order.setLineAttribute3(rs.getString(LINE_ATTRIB3));
		order.setLineAttribute7(rs.getString(LINE_ATTRIB7));
		order.setLineRetAttribute1(rs.getString(LINE_RET_ATTRIB1));
		order.setLineRetAttribute2(rs.getLong(LINE_RET_ATTRIB2));
		order.setLineRetAttribute3(rs.getString(LINE_RET_ATTRIB3));
		order.setLineRetAttribute4(rs.getLong(LINE_RET_ATTRIB4));
		order.setLineRetAttribute5(rs.getLong(LINE_RET_ATTRIB5));
		order.setLineStatus(rs.getString(LINE_STATUS));
		order.setServiceRefLineId(rs.getInt(SERVICE_REF_LINE_ID));
		order.setInventoryItemId(rs.getInt(INVENTORY_ITEM_ID));
		order.setUnitWeight(rs.getBigDecimal(UNIT_WEIGHT));
		order.setWeightUOMCode(rs.getString(WEIGHT_UOM_CODE));
		order.setItemSKU(rs.getString(ITEM_SKU));
		order.setItemType(rs.getString(ITEM_TYPE));
		order.setItemDescription(rs.getString(ITEM_DESCRIPTION));
		order.setItemCatalogGroupId(rs.getString(ITEM_CATALOG_GROUP_ID));
		order.setItemCategory(rs.getString(ITEM_CATEGORY));
		order.setPrimaryUnitOfMeasure(rs.getString(PRIMARY_UNIT_OF_MEASURE));
		order.setLocationId(rs.getInt(LOCATION_ID));
		order.setSwingByStore(rs.getString(SWINGBYSTORE));
		order.setCashReceiptId(rs.getInt(CASH_RECEIPT_ID));
		order.setReceiptDate(rs.getTimestamp(RECEIPT_DATE));
		order.setPosTxnNo(rs.getString(POSTXNNO));
		order.setPosRegNo(rs.getString(POSREGNO));
		order.setPosStoreNo(rs.getString(POSSTORENO));
		order.setTaxProvince(rs.getString(TAX_PROVINCE));
		order.setGstTax(rs.getBigDecimal(GSTTAX));
		order.setGstPercent(rs.getBigDecimal(GSTPERCENT));
		order.setPstTax(rs.getBigDecimal(PSTTAX));
		order.setPstPercent(rs.getBigDecimal(PSTPERCENT));
		order.setHstTax(rs.getBigDecimal(HSTTAX));
		order.setHstPercent(rs.getBigDecimal(HSTPERCENT));
		order.setReleaseStatus(rs.getString(RELEASED_STATUS));
		order.setQuantityShipped(rs.getInt(QUANTITY_SHIPPED));
		order.setQuantityReceived(rs.getInt(QUANTITY_RECEIVED));
	}
}